package io.framework.modules.air.utils;

import io.framework.common.utils.DateUtils;
import io.framework.modules.air.dto.AirCheckExplainDTO;
import io.framework.modules.air.dto.AirDTO;
import io.framework.modules.air.dto.AirDayNumberDTO;
import io.framework.modules.air.dto.AirNdChangeDTO;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

/**
 * 〈空气数据  读取数据〉
 *
 * @Author GJQ
 * @Create 2020/5/20
 * @Since 1.0.0
 */


public class AirFileCommonUtil {

    public static void main(String[] args) {

        List<AirDTO> airDTOList = AirFileCommonUtil.readAirData(3, "2020-04", "E:\\workspace\\projects_rongmo\\资料\\三亚环保项目\\Aaa第二版-新数据\\三亚数据资料\\数据报送20200513\\数据报送\\大气\\城市-监测数据.xls");
        System.out.println(airDTOList.toString());
//        List<AirDayNumberDTO> airDayNumberDTOList = readAirNumber("2020-04", "E:\\workspace\\projects_rongmo\\资料\\三亚环保项目\\Aaa第二版-新数据\\三亚数据资料\\数据报送20200513\\数据报送\\大气\\城市－优良天数.xls");
//        System.out.println(airDayNumberDTOList.toString());
    }

    /**
     * 获取空气站点数据
     * type : 1-城市站点-监测数据 2-旅游区站点-监测数据 3-城市-监测数据
     *
     * @param sheetNum
     * @param url
     * @param sheetNum
     * @return
     */
    public static List<AirDTO> readAirData(int sheetNum, String month, String url) {
        Workbook workbook = readExcel(url);
        if (workbook.getNumberOfSheets() <= sheetNum) return null;
        List<AirDTO> airDTOList = null;
        if (workbook != null) {
            airDTOList = new ArrayList<>();
            Sheet sheet = workbook.getSheetAt(sheetNum);
            int rown = sheet.getPhysicalNumberOfRows();
            int type = sheetNum + 1;
            Row row = null;
            for (int i = 1; i < rown; i++) {
                if (type == 3) {
                    if (i < 5) {
                        continue;
                    }
                } else {
                    if (i < 6) {
                        continue;
                    }
                }
                row = sheet.getRow(i);
                if (row != null) {
                    if (StringUtils.isNotBlank(getCellFormatValue(row.getCell(0)) + "") &&
                            StringUtils.isNotBlank(getCellFormatValue(row.getCell(1)) + "") &&
                            StringUtils.isNotBlank(getCellFormatValue(row.getCell(2)) + "")) {

                        //赋值
                        AirDTO airDTO = setAirValue(type, row);

                        if (airDTO != null && StringUtils.isNotBlank(airDTO.getCityName())) {
                            airDTO.setType(type);
                            airDTO.setMonth(DateUtils.parse(month, DateUtils.DATE_MONTH_PATTERN));
                            airDTOList.add(airDTO);
                        }
                    }
                }
            }
        }
        return airDTOList;
    }

    /**
     * 获取优良天数
     * 第三个sheet
     *
     * @param sheetNum
     * @param month
     * @param url
     * @return
     */
    public static List<AirDayNumberDTO> readAirNumber(int sheetNum, String month, String url) {
        Workbook workbook = readExcel(url);
        if (workbook.getNumberOfSheets() <= sheetNum) return null;
        List<AirDayNumberDTO> airNumberDTOS = null;
        if (workbook != null) {
            airNumberDTOS = new ArrayList<>();
            Sheet sheet = workbook.getSheetAt(sheetNum);
            int rown = sheet.getPhysicalNumberOfRows();
            Row row = null;
            for (int i = 1; i < rown; i++) {
                if (i < 5) {
                    continue;
                }
                row = sheet.getRow(i);
                if (row != null) {
                    if (StringUtils.isNotBlank(getCellFormatValue(row.getCell(0)) + "") &&
                            StringUtils.isNotBlank(getCellFormatValue(row.getCell(1)) + "") &&
                            StringUtils.isNotBlank(getCellFormatValue(row.getCell(2)) + "")) {

                        //赋值
                        AirDayNumberDTO airNumberDTO = setAirNumberValue(row);

                        if (airNumberDTO != null && StringUtils.isNotBlank(airNumberDTO.getCityName())) {
                            airNumberDTO.setMonth(DateUtils.parse(month, DateUtils.DATE_MONTH_PATTERN));
                            airNumberDTOS.add(airNumberDTO);
                        }
                    }
                }
            }
        }
        return airNumberDTOS;
    }

    /**
     * 获取浓度比较
     * 同比 / 环比
     *
     * @param url
     * @return
     */
    public static List<AirNdChangeDTO> readAirNdChange(int sheetNum, String month, String url) {
        Workbook workbook = readExcel(url);
        if (workbook.getNumberOfSheets() <= sheetNum) return null;
        List<AirNdChangeDTO> airNdChangeDTOS = null;
        if (workbook != null) {
            airNdChangeDTOS = new ArrayList<>();
            Sheet sheet = workbook.getSheetAt(sheetNum);
            int rown = sheet.getPhysicalNumberOfRows();
            Row row = null;
            Integer classify = null;
            int type = sheetNum + 1;
            for (int i = 1; i < rown; i++) {
                if (type == 5) {
                    if (i < 5) {
                        continue;
                    }
                    //环比
                    classify = 2;
                } else {
                    if (i < 6) {
                        continue;
                    }
                    //同比
                    classify = 1;
                }
                row = sheet.getRow(i);
                if (row != null) {
                    if (StringUtils.isNotBlank(getCellFormatValue(row.getCell(0)) + "") &&
                            StringUtils.isNotBlank(getCellFormatValue(row.getCell(1)) + "") &&
                            StringUtils.isNotBlank(getCellFormatValue(row.getCell(2)) + "")) {

                        //赋值
                        AirNdChangeDTO airNdChangeDTO = setAirNdChangeValue(classify, row);

                        if (airNdChangeDTO != null && StringUtils.isNotBlank(airNdChangeDTO.getCityName())) {
                            airNdChangeDTO.setMonth(DateUtils.parse(month, DateUtils.DATE_MONTH_PATTERN));
                            airNdChangeDTOS.add(airNdChangeDTO);
                        }
                    }
                }
            }
        }
        return airNdChangeDTOS;
    }

    /**
     * 获取监测数据审核情况说明
     * <p>
     * 第7个表格 审核情况说明
     *
     * @param month
     * @param url
     * @return
     */
    public static List<AirCheckExplainDTO> readAirCheckExplain(int sheetNum, String month, String url) {
        Workbook workbook = readExcel(url);
        if (workbook.getNumberOfSheets() <= sheetNum) return null;
        List<AirCheckExplainDTO> airCheckExplainDTOS = null;
        if (workbook != null) {
            airCheckExplainDTOS = new ArrayList<>();
            Sheet sheet = workbook.getSheetAt(sheetNum);
            int rown = sheet.getPhysicalNumberOfRows();
            Row row = null;
            for (int i = 1; i < rown; i++) {
                if (i < 4) {
                    continue;
                }
                row = sheet.getRow(i);
                if (row != null) {
                    if (StringUtils.isNotBlank(getCellFormatValue(row.getCell(0)) + "") &&
                            StringUtils.isNotBlank(getCellFormatValue(row.getCell(1)) + "") &&
                            StringUtils.isNotBlank(getCellFormatValue(row.getCell(2)) + "")) {

                        //赋值
                        AirCheckExplainDTO airCheckExplainDTO = setAirCheckExplainValue(row);

                        if (airCheckExplainDTO != null && StringUtils.isNotBlank(airCheckExplainDTO.getCityName()) && StringUtils.isNotBlank(airCheckExplainDTO.getStationName())) {
                            airCheckExplainDTO.setMonth(DateUtils.parse(month, DateUtils.DATE_MONTH_PATTERN));
                            airCheckExplainDTOS.add(airCheckExplainDTO);
                        }
                    }
                }
            }
        }
        return airCheckExplainDTOS;
    }


    public static AirDTO setAirValue(Integer type, Row row) {
        AirDTO airDTO = new AirDTO();
        int i = 0;
        airDTO.setCityName((String) getCellFormatValue(row.getCell(i++)));
        if (type == 1 || type == 2) {
            //报错  强制转换失败
            Object name = getCellFormatValue(row.getCell(i++));
            if (name instanceof String) {
                airDTO.setStationName(name.toString());
            } else {
                return null;
            }
        }
        Object date = getCellFormatValue(row.getCell(i++));
        if (date instanceof Date) {
            airDTO.setDate((Date) date);
        } else if (date instanceof String) {
            if (StringUtils.isNotEmpty(date.toString())) {
                airDTO.setDate(DateUtils.parse(date.toString(), DateUtils.DATE_PATTERN));
            }
        } else {
            return null;
        }

        airDTO.setSo2((BigDecimal) getCellFormatValue(row.getCell(i++)));
        airDTO.setSo2Zs((BigDecimal) getCellFormatValue(row.getCell(i++)));
        airDTO.setNo2((BigDecimal) getCellFormatValue(row.getCell(i++)));
        airDTO.setNo2Zs((BigDecimal) getCellFormatValue(row.getCell(i++)));
        airDTO.setPm10((BigDecimal) getCellFormatValue(row.getCell(i++)));
        airDTO.setPm10Zs((BigDecimal) getCellFormatValue(row.getCell(i++)));
        airDTO.setCo((BigDecimal) getCellFormatValue(row.getCell(i++)));
        airDTO.setCoZs((BigDecimal) getCellFormatValue(row.getCell(i++)));
        airDTO.setO3((BigDecimal) getCellFormatValue(row.getCell(i++)));
        airDTO.setO3Zs((BigDecimal) getCellFormatValue(row.getCell(i++)));
        airDTO.setPm25((BigDecimal) getCellFormatValue(row.getCell(i++)));
        airDTO.setPm25Zs((BigDecimal) getCellFormatValue(row.getCell(i++)));
        airDTO.setAqi((BigDecimal) getCellFormatValue(row.getCell(i++)));
        airDTO.setTop((String) getCellFormatValue(row.getCell(i++)));
        airDTO.setLevel((String) getCellFormatValue(row.getCell(i++)));
        airDTO.setClassify((String) getCellFormatValue(row.getCell(i++)));
        airDTO.setColorName((String) getCellFormatValue(row.getCell(i)));
        return airDTO;
    }

    public static AirDayNumberDTO setAirNumberValue(Row row) {
        AirDayNumberDTO airNumberDTO = new AirDayNumberDTO();
        int i = 0;
        Object name = getCellFormatValue(row.getCell(i++));
        if (name instanceof String) {
            airNumberDTO.setCityName(name.toString());
        } else {
            return null;
        }
        Object o = getCellFormatValue(row.getCell(i++));
        if (o instanceof String) {
            return null;
        }
        airNumberDTO.setUserfulTongbi((BigDecimal) o);
        airNumberDTO.setUserfulHuanbi((BigDecimal) getCellFormatValue(row.getCell(i++)));
        Object b = getCellFormatValue(row.getCell(i++));
        if (b instanceof String) {
            return null;
        }
        airNumberDTO.setUserfulNum((BigDecimal) b);
        airNumberDTO.setYouTongbi((BigDecimal) getCellFormatValue(row.getCell(i++)));
        airNumberDTO.setYouHuanbi((BigDecimal) getCellFormatValue(row.getCell(i++)));
        airNumberDTO.setYouNum((BigDecimal) getCellFormatValue(row.getCell(i++)));
        airNumberDTO.setLiangTongbi((BigDecimal) getCellFormatValue(row.getCell(i++)));
        airNumberDTO.setLiangHuanbi((BigDecimal) getCellFormatValue(row.getCell(i++)));
        airNumberDTO.setLiangNum((BigDecimal) getCellFormatValue(row.getCell(i++)));
        airNumberDTO.setQingdTongbi((BigDecimal) getCellFormatValue(row.getCell(i++)));
        airNumberDTO.setQingdHuanbi((BigDecimal) getCellFormatValue(row.getCell(i++)));
        airNumberDTO.setQingdNum((BigDecimal) getCellFormatValue(row.getCell(i)));
        return airNumberDTO;
    }

    public static AirNdChangeDTO setAirNdChangeValue(Integer classify, Row row) {
        AirNdChangeDTO airNdChangeDTO = new AirNdChangeDTO();
        int i = 0;
        BigDecimal now = null;
        BigDecimal last = null;
        Object name = getCellFormatValue(row.getCell(i++));
        if (name instanceof String) {
            airNdChangeDTO.setCityName(name.toString());
        } else {
            return null;
        }
        Object o = getCellFormatValue(row.getCell(i++));
        if (o instanceof String) {
            return null;
        }
        now = (BigDecimal) o;
        last = (BigDecimal) getCellFormatValue(row.getCell(i++));
        airNdChangeDTO.setSo2Now(now);
        airNdChangeDTO.setSo2Last(last);
        i++;
        // (now-last)/last * 100
        airNdChangeDTO.setSo2Change(getNdChange(now, last));
        Object a = getCellFormatValue(row.getCell(i++));
        if (a instanceof String) {
            return null;
        }
        now = (BigDecimal) a;
        last = (BigDecimal) getCellFormatValue(row.getCell(i++));
        i++;
        airNdChangeDTO.setNo2Now(now);
        airNdChangeDTO.setNo2Last(last);
        airNdChangeDTO.setNo2Change(getNdChange(now, last));
        now = (BigDecimal) getCellFormatValue(row.getCell(i++));
        last = (BigDecimal) getCellFormatValue(row.getCell(i++));
        i++;
        airNdChangeDTO.setPm10Now(now);
        airNdChangeDTO.setPm10Last(last);
        airNdChangeDTO.setPm10Change(getNdChange(now, last));
        now = (BigDecimal) getCellFormatValue(row.getCell(i++));
        last = (BigDecimal) getCellFormatValue(row.getCell(i++));
        i++;
        airNdChangeDTO.setCoNow(now);
        airNdChangeDTO.setCoLast(last);
        airNdChangeDTO.setCoChange(getNdChange(now, last));
        now = (BigDecimal) getCellFormatValue(row.getCell(i++));
        last = (BigDecimal) getCellFormatValue(row.getCell(i++));
        i++;
        airNdChangeDTO.setO3Now(now);
        airNdChangeDTO.setO3Last(last);
        airNdChangeDTO.setO3Change(getNdChange(now, last));
        now = (BigDecimal) getCellFormatValue(row.getCell(i++));
        last = (BigDecimal) getCellFormatValue(row.getCell(i));
        airNdChangeDTO.setPm25Now(now);
        airNdChangeDTO.setPm25Last(last);
        airNdChangeDTO.setPm25Change(getNdChange(now, last));
        airNdChangeDTO.setClassify(classify);
        return airNdChangeDTO;
    }

    public static AirCheckExplainDTO setAirCheckExplainValue(Row row) {
        AirCheckExplainDTO airCheckExplainDTO = new AirCheckExplainDTO();
        int i = 0;
        airCheckExplainDTO.setCityName((String) getCellFormatValue(row.getCell(i++)));
        airCheckExplainDTO.setStationName((String) getCellFormatValue(row.getCell(i++)));
        airCheckExplainDTO.setMonitorFactors((String) getCellFormatValue(row.getCell(i++)));
        airCheckExplainDTO.setRemark((String) getCellFormatValue(row.getCell(i)));
        return airCheckExplainDTO;
    }

    public static Object getCellFormatValue(Cell cell) {
        Object cellValue = null;
        if (cell != null) {
            // 如果是字符串，直接获取值
            CellType cellType = cell.getCellTypeEnum();
            if (cellType.equals(CellType.STRING)) {
                cellValue = cell.getStringCellValue().replaceAll(" ", "");
            } else if (cellType.equals(CellType.NUMERIC)) {
                if (DateUtil.isCellDateFormatted(cell)) {
                    //转换为日期格式YYYY-mm-dd
                    cellValue = cell.getDateCellValue();
                } else {
                    //数字
                    cellValue = cell.getNumericCellValue();
                    if (cellValue instanceof Integer) {
                        cellValue = new BigDecimal((Integer) cellValue);
                    } else {
                        cellValue = new BigDecimal(cellValue + "");
                    }
                }
            }
            if ("NA".equals(cellValue)) {
                cellValue = new BigDecimal(0);
            }
        } else {
            cellValue = "";
        }
        return cellValue;
    }

    //读取excel
    public static Workbook readExcel(String filePath) {
        Workbook wb = null;
        if (filePath == null) {
            return null;
        }
        String extString = filePath.substring(filePath.lastIndexOf("."));
        InputStream is = null;
        try {
            is = new FileInputStream(filePath);
            if (".xls".equals(extString)) {
                return wb = new HSSFWorkbook(is);
            } else if (".xlsx".equals(extString)) {
                return wb = new XSSFWorkbook(is);
            } else {
                return wb = null;
            }
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
        return wb;
    }

    private static BigDecimal getNdChange(BigDecimal now, BigDecimal last) {
        // (now-last)/last * 100
        BigDecimal change = null;
        if (now != null && last != null) {
            BigDecimal zero = new BigDecimal(0);
            if (last.compareTo(zero) == 0) {
                return zero;
            }
            change = ((now.subtract(last)).divide(last, 3, BigDecimal.ROUND_HALF_UP)).multiply(new BigDecimal(100));
        }
        return change;
    }
}
